package top.xzxsrq.exsyExcelTool.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.xzxsrq.common.utils.FileUtilsZX;
import top.xzxsrq.common.utils.ObjectUtilsZX;
import top.xzxsrq.exsyExcelTool.ReadListener.ObjectReadListener;

import java.io.*;
import java.util.Iterator;
import java.util.List;
import java.util.function.Consumer;

/**
 * @program: MyUtils
 * @create: 2021-12-08 09:31
 **/
@Slf4j
public abstract class EasyExcelUtilsZX {
    /**
     * 传入表头定义类， 返回最高的表头层级
     *
     * @param clazz
     * @return
     */
    public static int getHeadMaxRowNumber(Class<?> clazz) {
        return ObjectUtilsZX
                .getFieldsAndSup(clazz)
                .stream()
                .map(i -> {
                    ExcelProperty annotation = i.getAnnotation(ExcelProperty.class);
                    if (annotation == null) {
                        return 0;
                    }
                    return annotation.value().length;
                })
                .reduce(0, (a, b) -> {
                    if (a > b) {
                        return a;
                    } else {
                        return b;
                    }
                });
    }

    /**
     * 复制wbSheet， 样式也会复制， 复制整个sheet，不是添加到目标sheet后面，<br/>
     * 而是覆盖目标sheet，如果目标sheet没有sheetName就创建，有级删除重新创建, <br/>
     * destSheetName为null时候使用sheetName的值为destFileName的sheet名
     *
     * @param srcFile       源文件
     * @param destFile      目标文件
     * @param sheetName     源sheetName 请保证唯一
     * @param destSheetName 目标文件目标sheetName，没有就创建 请保证唯一， 有就删除重新创建
     * @throws Exception
     */
    public static void copyWbSheet(File srcFile, File destFile,
                                   String sheetName, String destSheetName) throws Exception {
        sheetCommon(srcFile, destFile, sheetName, destSheetName, true, true, false, -1);
    }

    /**
     * sheet的复制或者追加
     *
     * @param srcFile
     * @param destFile
     * @param sheetName
     * @param destSheetName
     * @param remove        是否移除目标sheet
     * @param copy          是否进行复制，或者是追加
     * @param appendType    追加类型 true表示行追加
     * @param separate      隔开
     * @throws Exception
     */
    private static void sheetCommon(File srcFile, File destFile,
                                    String sheetName, String destSheetName,
                                    boolean remove, boolean copy,
                                    boolean appendType, int separate) throws Exception {
        if (FileUtilsZX.notExists(srcFile)) {
            throw new RuntimeException("源文件不存在");
        }
        XSSFWorkbook srcwb = new XSSFWorkbook(srcFile);
        XSSFSheet srcSheet;
        if (sheetName == null) {
            srcSheet = srcwb.getSheetAt(0);
            sheetName = srcwb.getSheetName(0);
        } else {
            srcSheet = srcwb.getSheet(sheetName);
        }
        if (srcSheet == null) {
            throw new RuntimeException("源文件sheet不存在");
        }
        if (destSheetName == null) {
            destSheetName = sheetName;
        }
        XSSFWorkbook destwb;
        XSSFSheet destSheet;
        // 不存在则新建
        if (!destFile.exists()) {
            destFile = createEmptyFile(destFile.getAbsolutePath());
            log.warn("程序创建的空文件样式可能会不一样，所以不建议使用程序创建");
        }
        destwb = new XSSFWorkbook(new FileInputStream(destFile));
        if (remove) {
            removeModelSheet(destwb, destSheetName);
        }
        destSheet = destwb.getSheet(destSheetName);
        if (destSheet == null) {
            destSheet = destwb.createSheet(destSheetName);
        }
        StyleMapping styleMapping = copyCellStyle(srcwb, destwb);
        if (copy) {
            copySheet(srcSheet, destSheet, styleMapping);
        } else { // 追加
            appendSheet(srcSheet, destSheet, styleMapping, appendType, separate);
        }
        // 保存
        saveFile(destwb, destFile);
    }

    /**
     * @param srcSheet
     * @param destSheet
     * @param styleMapping
     * @param appendType   true表示行追加
     * @param separate     隔开多少 如果小于等于 0 则不创建
     */
    public static void appendSheet(XSSFSheet srcSheet, XSSFSheet destSheet, StyleMapping styleMapping, boolean appendType, int separate) {
        Iterator<Row> rowIterator = srcSheet.rowIterator();
        if (separate <= 0) {
            separate = 0;
        }
        if (appendType) {
            separate++;
            int lastRowNum = destSheet.getLastRowNum();
            int areadlyColunm = 0;
            int beginRowNum = lastRowNum == 0 ? 0 : lastRowNum + separate;
            //合并区域处理
            appendMergeRegion(srcSheet, destSheet, appendType, beginRowNum);
            while (rowIterator.hasNext()) {
                Row next = rowIterator.next();
                XSSFRow destRow = destSheet.createRow(beginRowNum++);
                copyRow(next, destRow, styleMapping);
                //调整列宽(增量调整)
                if (next.getPhysicalNumberOfCells() > areadlyColunm) {
                    for (int i = areadlyColunm; i < next.getPhysicalNumberOfCells(); i++) {
                        destSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
                    }
                    areadlyColunm = next.getPhysicalNumberOfCells();
                }
            }
        } else {
            // 获取最大
            short maxColPos = getMaxColPos(destSheet);
            short colPos = maxColPos <= 0 ? 0 : (short) (maxColPos + separate);
            //合并区域处理
            appendMergeRegion(srcSheet, destSheet, appendType, colPos);
            while (rowIterator.hasNext()) {
                Row next = rowIterator.next();
                Iterator<Cell> cellIterator = next.cellIterator();
                while (cellIterator.hasNext()) {
                    XSSFRow row = destSheet.getRow(next.getRowNum());
                    if (row == null) {
                        row = destSheet.createRow(next.getRowNum());
                    }
                    XSSFCell destCell = row.createCell(colPos++);
                    Cell srcCell = cellIterator.next();
                    copyCell(srcCell, destCell, styleMapping);
                    destSheet.setColumnWidth(destCell.getColumnIndex(), srcSheet.getColumnWidth(srcCell.getColumnIndex()));
                }
                colPos = maxColPos <= 0 ? 0 : (short) (maxColPos + separate);
            }
        }
    }

    public static short getMaxColPos(XSSFSheet sheet) {
        short result = -1;
        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            short lastCellNum = next.getLastCellNum();
            if (lastCellNum > result) {
                result = lastCellNum;
            }
        }
        return result;
    }

    /**
     * 把源sheet添加到目标sheet的末尾， 如果目标sheet不存在就创建一个<br/>
     * destSheetName为null时候使用sheetName的值为destFileName的sheet名
     *
     * @param srcFile
     * @param destFile
     * @param sheetName     请保证唯一
     * @param destSheetName 请保证唯一
     * @param appendType    true表示行追加
     * @param separate      隔开
     */
    public static void sheetAppendSheet(File srcFile, File destFile, String sheetName, String destSheetName, boolean appendType, int separate) throws Exception {
        sheetCommon(srcFile, destFile, sheetName, destSheetName, false, false, appendType, separate);
    }

    /**
     * 把源sheet添加到目标sheet的末尾, 行追加，隔开 1 行
     *
     * @param srcFile
     * @param destFile
     * @param sheetName
     * @param destSheetName
     * @throws Exception
     */
    public static void sheetAppendSheet(File srcFile, File destFile, String sheetName, String destSheetName) throws Exception {
        sheetAppendSheet(srcFile, destFile, sheetName, destSheetName, true, 1);
    }

    public static void saveFile(XSSFWorkbook destwb, File file) throws IOException {
        FileOutputStream fileOutStream = new FileOutputStream(file);
        destwb.write(fileOutStream);
        fileOutStream.close();
    }

    /**
     * sheet 复制，复制数据、如果同一个文件，复制样式，不同文件则只复制数据<br/>
     * 如果是同book中复制，建议使用workbook中的cloneSheet()方法<br/>
     * <p>
     * <br/>建议用于 不同book间只复制数据
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet) {
        copySheet(srcSheet, desSheet, true, true, null);
    }

    /**
     * sheet 复制，如果同一个文件，复制样式，不同文件则不复制<br/>
     * <p>
     * <br/>建议用于 同book中，只复制样式，不复制数据<br/>
     * eg: copySheet(srcSheet, desSheet, false)
     *
     * @param copyValueFlag 控制是否复制数据
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyValueFlag) {
        copySheet(srcSheet, desSheet, copyValueFlag, true, null);
    }

    /**
     * sheet 复制，复制数据、样式<br/>
     * <p>
     * <br/>建议用于 不同book间复制，同时复制数据和样式<br/>
     * eg: copySheet(srcSheet, desSheet, mapping)
     *
     * @param mapping 不同文件间复制时，如果要复制样式，必传，否则不复制样式
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, StyleMapping mapping) {
        copySheet(srcSheet, desSheet, true, true, mapping);
    }

    /**
     * sheet 复制,复制数据<br/>
     * <p>
     * <br/>建议用于 同book中，只复制数据，不复制样式<br/>
     * eg: copySheet(srcSheet, desSheet, false, null)
     *
     * @param srcSheet
     * @param desSheet
     * @param copyStyleFlag
     * @param mapping
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyStyleFlag, StyleMapping mapping) {
        copySheet(srcSheet, desSheet, true, copyStyleFlag, mapping);
    }


    /**
     * sheet 复制, 灵活控制是否控制数据、样式<br/>
     * <p>
     * <br/>不建议直接使用
     *
     * @param copyValueFlag 控制是否复制数据
     * @param copyStyleFlag 控制是否复制样式
     * @param mapping       不同book中复制样式时，必传
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        if (srcSheet.getWorkbook() == desSheet.getWorkbook()) {
            log.warn("统一workbook内复制sheet建议使用 workbook的cloneSheet方法");
        }

        //合并区域处理
        copyMergedRegion(srcSheet, desSheet);

        //行复制
        Iterator<Row> rowIterator = srcSheet.rowIterator();

        int areadlyColunm = 0;
        while (rowIterator.hasNext()) {
            Row srcRow = rowIterator.next();
            Row desRow = desSheet.createRow(srcRow.getRowNum());
            copyRow(srcRow, desRow, copyValueFlag, copyStyleFlag, mapping);

            //调整列宽(增量调整)
            if (srcRow.getPhysicalNumberOfCells() > areadlyColunm) {
                for (int i = areadlyColunm; i < srcRow.getPhysicalNumberOfCells(); i++) {
                    desSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
                }
                areadlyColunm = srcRow.getPhysicalNumberOfCells();
            }
        }
    }

    /**
     * 复制行
     */
    public static void copyRow(Row srcRow, Row desRow) {
        copyRow(srcRow, desRow, true, true, null);
    }

    /**
     * 复制行
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyValueFlag) {
        copyRow(srcRow, desRow, copyValueFlag, true, null);
    }

    /**
     * 复制行
     */
    public static void copyRow(Row srcRow, Row desRow, StyleMapping mapping) {
        copyRow(srcRow, desRow, true, true, mapping);
    }

    /**
     * 复制行
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyStyleFlag, StyleMapping mapping) {
        copyRow(srcRow, desRow, true, copyStyleFlag, mapping);
    }

    /**
     * 复制行
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        Iterator<Cell> it = srcRow.cellIterator();
        while (it.hasNext()) {
            Cell srcCell = it.next();
            Cell desCell = desRow.createCell(srcCell.getColumnIndex());
            copyCell(srcCell, desCell, copyValueFlag, copyStyleFlag, mapping);
        }
    }

    /**
     * 复制区域（合并单元格）
     */
    public static void copyMergedRegion(Sheet srcSheet, Sheet desSheet) {
        int sheetMergerCount = srcSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i);
            desSheet.addMergedRegion(cellRangeAddress);
        }
    }

    /**
     * 追加合并的单元格
     *
     * @param srcSheet
     * @param destSheet
     * @param appentType true表示行追加
     * @param beginPos   合并单元格开始的位置
     */
    public static void appendMergeRegion(Sheet srcSheet, Sheet destSheet, boolean appentType, int beginPos) {
        int numMergedRegions = srcSheet.getNumMergedRegions();
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress mergedRegion = srcSheet.getMergedRegion(i);
            if (appentType) {
                int firstRow = mergedRegion.getFirstRow();
                int lastRow = mergedRegion.getLastRow();
                mergedRegion.setFirstRow(firstRow + beginPos);
                mergedRegion.setLastRow(lastRow + beginPos);
                destSheet.addMergedRegion(mergedRegion);
            } else {
                int firstColumn = mergedRegion.getFirstColumn();
                int lastColumn = mergedRegion.getLastColumn();
                mergedRegion.setFirstColumn(firstColumn + beginPos);
                mergedRegion.setLastColumn(lastColumn + beginPos);
                destSheet.addMergedRegion(mergedRegion);
            }
        }
    }

    /**
     * 复制单元格，复制数据，如果同文件，复制样式，不同文件则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell) {
        copyCell(srcCell, desCell, true, true, null);
    }

    /**
     * 复制单元格， 如果同文件，复制样式，不同文件则不复制样式
     *
     * @param copyValueFlag 控制是否复制数据
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyValueFlag) {
        copyCell(srcCell, desCell, copyValueFlag, true, null);
    }

    /**
     * 复制单元格，复制数据,复制样式
     *
     * @param mapping 不同文件间复制时，如果要复制样式，必传，否则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell, StyleMapping mapping) {
        copyCell(srcCell, desCell, true, true, mapping);
    }

    /**
     * 复制单元格，复制数据
     *
     * @param copyStyleFlag 控制是否复制样式
     * @param mapping       不同文件间复制时，如果要复制样式，必传，否则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyStyleFlag, StyleMapping mapping) {
        copyCell(srcCell, desCell, true, copyStyleFlag, mapping);
    }

    /**
     * 复制单元格
     *
     * @param copyValueFlag 控制是否复制单元格的内容
     * @param copyStyleFlag 控制是否复制样式
     * @param mapping       不同文件间复制时，如果需要连带样式复制，必传，否则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        Workbook srcBook = srcCell.getSheet().getWorkbook();
        Workbook desBook = desCell.getSheet().getWorkbook();

        //复制样式
        //如果是同一个excel文件内，连带样式一起复制
        if (srcBook == desBook && copyStyleFlag) {
            //同文件，复制引用
            desCell.setCellStyle(srcCell.getCellStyle());
        } else if (copyStyleFlag) {
            //不同文件，通过映射关系复制
            if (null != mapping) {
                short desIndex = mapping.desIndex(srcCell.getCellStyle().getIndex());
                desCell.setCellStyle(desBook.getCellStyleAt(desIndex));
            }
        }

        //复制评论
        if (srcCell.getCellComment() != null) {
            desCell.setCellComment(srcCell.getCellComment());
        }

        //复制内容
        desCell.setCellType(srcCell.getCellTypeEnum());

        if (copyValueFlag) {
            switch (srcCell.getCellTypeEnum()) {
                case STRING:
                    desCell.setCellValue(srcCell.getStringCellValue());
                    break;
                case NUMERIC:
                    desCell.setCellValue(srcCell.getNumericCellValue());
                    break;
                case FORMULA:
                    desCell.setCellFormula(srcCell.getCellFormula());
                    break;
                case BOOLEAN:
                    desCell.setCellValue(srcCell.getBooleanCellValue());
                    break;
                case ERROR:
                    desCell.setCellValue(srcCell.getErrorCellValue());
                    break;
                case BLANK:
                    //nothing to do
                    break;
                default:
                    break;
            }
        }

    }


    /**
     * 把一个excel中的styleTable复制到另一个excel中<br>
     * 如果是同一个excel文件，就不用复制styleTable了
     *
     * @return StyleMapping 两个文件中styleTable的映射关系
     * @see StyleMapping
     */
    public static StyleMapping copyCellStyle(Workbook srcBook, Workbook desBook) {
        if (null == srcBook) {
            throw new RuntimeException("源excel 不存在");
        }
        if (null == desBook) {
            throw new RuntimeException("目标excel 不存在");
        }
        if (srcBook.equals(desBook)) {
            throw new RuntimeException("不要使用此方法在同一个文件中copy style，同一个excel中复制sheet不需要copy Style");
        }
        if ((srcBook instanceof HSSFWorkbook && desBook instanceof XSSFWorkbook) ||
                (srcBook instanceof XSSFWorkbook && desBook instanceof HSSFWorkbook)) {
            throw new RuntimeException("不支持在不同的版本的excel中复制样式）");
        }

        log.debug("src中style number:{}, des中style number:{}", srcBook.getNumCellStyles(), desBook.getNumCellStyles());
        short[] src2des = new short[srcBook.getNumCellStyles()];
        short[] des2src = new short[desBook.getNumCellStyles() + srcBook.getNumCellStyles()];

        for (short i = 0; i < srcBook.getNumCellStyles(); i++) {
            //建立双向映射
            CellStyle srcStyle = srcBook.getCellStyleAt(i);
            CellStyle desStyle = desBook.createCellStyle();
            src2des[srcStyle.getIndex()] = desStyle.getIndex();
            des2src[desStyle.getIndex()] = srcStyle.getIndex();

            //复制样式
            desStyle.cloneStyleFrom(srcStyle);
        }


        return new StyleMapping(des2src, src2des);
    }

    /**
     * 存放两个excel文件中的styleTable的映射关系，以便于在复制表格时，在目标文件中获取到对应的样式
     */
    public static class StyleMapping {
        /**
         *
         */
        private short[] des2srcIndexMapping;
        /**
         *
         */
        private short[] src2desIndexMapping;

        /**
         * 不允许其他类创建此类型对象
         */
        private StyleMapping() {
        }

        public StyleMapping(short[] des2srcIndexMapping, short[] src2desIndexMapping) {
            this.des2srcIndexMapping = des2srcIndexMapping;
            this.src2desIndexMapping = src2desIndexMapping;
        }

        public short srcIndex(short desIndex) {
            if (desIndex < 0 || desIndex >= this.des2srcIndexMapping.length) {
                throw new RuntimeException("索引越界：源文件styleNum=" + this.des2srcIndexMapping.length + " 访问位置=" + desIndex);
            }
            return this.des2srcIndexMapping[desIndex];
        }

        /**
         * 根据源文件的style的index,获取目标文件的style的index
         *
         * @param srcIndex 源excel中style的index
         * @return desIndex 目标excel中style的index
         */
        public short desIndex(short srcIndex) {
            if (srcIndex < 0 || srcIndex >= this.src2desIndexMapping.length) {
                throw new RuntimeException("索引越界：源文件styleNum=" + this.src2desIndexMapping.length + " 访问位置=" + srcIndex);
            }

            return this.src2desIndexMapping[srcIndex];
        }
    }

    public static void removeModelSheet(Workbook wb, int id) {
        if (id < 0) {
            return;
        }
        wb.removeSheetAt(id);
    }

    public static void removeModelSheet(Workbook wb, String name) {
        Sheet sheet = wb.getSheet(name);
        while (sheet != null) {
            int sheetIndex = wb.getSheetIndex(sheet);
            removeModelSheet(wb, sheetIndex);
            sheet = wb.getSheet(name);
        }
    }

    public static void removeModelSheet(Workbook wb, Sheet noDelSheet) {
        int sheetIndex = wb.getSheetIndex(noDelSheet);
        removeModelSheet(wb, sheetIndex);
    }

    /**
     * 创建一个空的excel文件
     *
     * @param path
     * @return
     * @throws IOException
     */
    public static File createEmptyFile(String path) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        File file = new File(path);
        FileOutputStream out = new FileOutputStream(file);
        workbook.write(out);
        return file;
    }

    public static <T> void readData(Class<T> head, InputStream inputStream, Consumer<T> callback) {
        EasyExcel.read(inputStream, head, new ObjectReadListener<T>(callback))
                .sheet()
                .doRead();
    }

    public static <T> List<T> readDataAll(Class<T> head, InputStream inputStream) {
        return EasyExcel.read(inputStream).head(head).sheet().doReadSync();
    }

}
